package com.joinway.framework.data.jdbc.service;

import java.util.Arrays;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.joinway.framework.bean.domain.constant.SqlConstants;
import com.joinway.framework.bean.exception.InternalException;
import com.joinway.framework.data.jdbc.bean.ConditionItem;
import com.joinway.framework.data.jdbc.bean.Criteria;
import com.joinway.framework.data.jdbc.bean.CriteriaItem;
import com.joinway.framework.data.jdbc.bean.FieldItem;
import com.joinway.framework.data.jdbc.bean.OrderItem;
import com.joinway.framework.data.jdbc.bean.QueryCriteria;
import com.joinway.framework.data.jdbc.bean.SaveCriteria;
import com.joinway.framework.data.jdbc.bean.SelectClause;
import com.joinway.framework.data.jdbc.bean.SelectResultSet;
import com.joinway.framework.data.jdbc.bean.WhereClause;
import com.joinway.framework.data.jdbc.repository.TableRepository;
import com.joinway.framework.extension.groovy.DomainUtils;
import com.joinway.framework.extension.groovy.GroovyUtils;
import com.joinway.framework.extension.utils.DataUtils;

public class SqlService extends AbstractSqlService {
	private final static Logger log = LoggerFactory.getLogger(SqlService.class);
	
	protected final static List<String> RESERVED_VARS = Arrays.asList(new String[]{"current_time", "current_timestamp", "now()", "null"});
	
	public SqlService(TableRepository repository) {
		super(repository);
	}

	public List<?> select(String sql) throws Exception {
		List<?> domains = repository.find(sql);
		return domains;
	}
	
	@Deprecated public List<?> select(QueryCriteria criteria, String sql) throws Exception {
		WhereClause wc = parseWhere(criteria);
		
		List<?> domains;
		
		if(criteria.getPage() == null || criteria.getRows() == null){
			domains = repository.find(sql, wc.getParams());
		}else{
			domains = repository.find(sql, criteria.getPage(), criteria.getRows(), wc.getParams());
		}
	
		domains.stream().forEach(d -> GroovyUtils.removeASTFields(d));
		
		return domains;
	}
	
	public SelectResultSet selectRecords(SelectClause sc) throws Exception {
		SelectResultSet result = new SelectResultSet();
		
		List<?> records = select(sc);
		result.setRecords(records);
		
		int count = selectCount(sc);
		result.setCount(count);
		
		return result;
	}
	
	public List<?> select(SelectClause sc) throws Exception {
		List<?> domains;
		
		String sql = sc.toString();
		WhereClause wc = sc.getWhereClause();
		
		if(sc.getPage() == null || sc.getRows() == null){
			if(wc != null){
				domains = repository.find(sql, wc.getParams());
			}else{
				domains = repository.find(sql);
			}
		}else{
			if(wc != null){
				domains = repository.find(sql, sc.getPage(), sc.getRows(), wc.getParams());
			}else{
				domains = repository.find(sql, sc.getPage(), sc.getRows());
			}
		}
	
		domains.stream().forEach(d -> GroovyUtils.removeASTFields(d));
		
		return domains;
	}
	
	public List<?> select(QueryCriteria criteria) throws Exception {
		String sql = parseSelect(criteria);
		
		WhereClause wc = parseWhere(criteria);
		
		sql += wc.getWhere();
		
		sql += parseOrder(criteria);
		
		List<?> domains;
		
		if(criteria.getPage() == null || criteria.getRows() == null){
			domains = repository.find(sql, wc.getParams());
		}else{
			domains = repository.find(sql, criteria.getPage(), criteria.getRows(), wc.getParams());
		}
	
		domains.stream().forEach(d -> GroovyUtils.removeASTFields(d));
		
		return domains;
	}
	
	@Deprecated public int selectCount(QueryCriteria query) throws Exception {
		String table = query.isRawName() ? query.getTable() : DomainUtils.convertToDBName(query.getTable(), query.isLowerCase());
		WhereClause wc = parseWhere(query);
		return getDataCount(table, wc);
	}
	
	public int selectCount(SelectClause sc) throws Exception {
		return getDataCount(sc);
	}
	
	public int persist(SaveCriteria criteria) throws Exception {
		if(CollectionUtils.isEmpty(criteria.getTargets())) return 0;
		
		String tableName = criteria.isRawName() ? criteria.getTable() : DomainUtils.convertToDBName(criteria.getTable(), criteria.isLowerCase());
		
		List<Object> params = new LinkedList<>();
		String setClause = "";
		int count = 0;
		
		if(SqlConstants.PersistenceAction.UpdateOrInsert.equalsIgnoreCase(criteria.getAction()) 
			|| SqlConstants.PersistenceAction.Update.equalsIgnoreCase(criteria.getAction())){
			
			Iterator<CriteriaItem> items = criteria.getTargets().iterator();
			while(items.hasNext()){
				CriteriaItem it = items.next();
				if(StringUtils.isNotEmpty(DataUtils.convertToString(it.getValue()))){
					String columnName = criteria.isRawName() ? it.getField() : DomainUtils.convertToDBName(it.getField());
					
					if(!RESERVED_VARS.contains(it.getValue())){
						setClause += StringUtils.join(columnName, " = ?,");
						params.add(it.getValue());
					}else{
						setClause += StringUtils.join(columnName, " = ", it.getValue(), ",");
					}
				}
			}
			
			setClause = setClause.substring(0, setClause.length() - 1);
			
			WhereClause wc = parseWhere(criteria, params);
			
			if(StringUtils.isNotBlank(wc.getWhere().trim())){
				String sql = StringUtils.join("update ", tableName, " set ", setClause ," ", wc.getWhere());
				count = repository.update(sql, params);
			}else{
				throw new InternalException("can't update whole table!");
			}
		}
		
		if(SqlConstants.PersistenceAction.UpdateOrInsert.equalsIgnoreCase(criteria.getAction()) 
			|| SqlConstants.PersistenceAction.Insert.equalsIgnoreCase(criteria.getAction())){
			if(count == 0){
				log.debug("no data was updated, will insert data");
				
				params = new LinkedList<>();
				String columnClause = "";
				List<String> holders = new LinkedList<>();
				
				Iterator<CriteriaItem> items = criteria.getTargets().iterator();
				while(items.hasNext()){
					CriteriaItem it = items.next();
					if(StringUtils.isNotEmpty(DataUtils.convertToString(it.getValue()))){
						String columnName = criteria.isRawName() ? it.getField() : DomainUtils.convertToDBName(it.getField());
						columnClause += StringUtils.join(columnName, ",");
						
						if(!RESERVED_VARS.contains(it.getValue())){
							params.add(it.getValue());
							holders.add("?");
						}else{
							holders.add(DataUtils.convertToString(it.getValue()));
						}
					}
				}
				
				columnClause = columnClause.substring(0, columnClause.length() - 1);
				String sql = StringUtils.join("insert into ", tableName ," (", columnClause ,") values (" + StringUtils.join(holders, ",") + ")");
				List<List<Object>> rows = repository.insert(sql, params);
				count = rows.size();
			}
		}
			
		return count;
	}
	
	public int delete(Criteria criteria) throws Exception {
		int count = 0;
		
		String tableName = criteria.isRawName() ? criteria.getTable() : DomainUtils.convertToDBName(criteria.getTable(), criteria.isLowerCase());
		
		WhereClause wc = parseWhere(criteria);
		
		if(StringUtils.isNotBlank(wc.getWhere().trim())){
			String sql = StringUtils.join("delete from ", tableName ," ", wc.getWhere());
			count = repository.delete(sql, wc.getParams());
		}else{
			throw new InternalException("can't delete whole table!");
		}
		
		return count;
	}
	
	@SuppressWarnings("unchecked")
	public String parseSelect(QueryCriteria criteria){
		List<String> fields = new LinkedList<>();
		
		if(criteria.getFields() instanceof List){
			List<String> passedFields = (List<String>)criteria.getFields();
			Iterator<String> items = passedFields.iterator();
			while(items.hasNext()){
				String it = items.next();
				String column = criteria.isRawName() ? it : DomainUtils.convertToDBName(it, criteria.isLowerCase());
				fields.add(column);
			}
		}else{
			String fieldValue = DataUtils.convertToString(criteria.getFields());
			String column = criteria.isRawName() ? fieldValue : DomainUtils.convertToDBName(fieldValue, criteria.isLowerCase());
			fields.add(column);
		}
		
		String distinct = criteria.isDistinct() ? "distinct" : "";
		String table = criteria.isRawName() ? criteria.getTable() : DomainUtils.convertToDBName(criteria.getTable(), criteria.isLowerCase());
		
		String sql = StringUtils.join("select ", distinct, " ", StringUtils.join(fields, ","), " from ", table);
		return sql;
	}
	
	public static WhereClause parseWhere(Criteria criteria){
		return parseWhere(criteria, new LinkedList<>());
	}
	
	public static WhereClause parseWhere(Criteria criteria, List<Object> params){
		WhereClause wc = new WhereClause();
		
		String where = "";
		
		if(CollectionUtils.isNotEmpty(criteria.getItems())){
			for(int i = 0; i < criteria.getItems().size(); i++){
				ConditionItem e = criteria.getItems().get(i);
//			criteria.items.eachWithIndex {ConditionItem e, int i ->
//				println e
				
				String columnName = getColumnName(criteria, e);
				
				String type = StringUtils.isNotBlank(e.getType()) ? e.getType().toLowerCase() : SqlConstants.DataType.Text;
				
				if(StringUtils.isNotBlank(DataUtils.convertToString(e.getValue()))){
					Object value = getFieldValue(e.getValue());
					
					if(SqlConstants.Operator.Between.equalsIgnoreCase(e.getOperator())){
//						println e.value
						
						List<?> values = new LinkedList<>();
						
						if(e.getValue() instanceof List){
							values = (List<?>)e.getValue();
						}else if(e.getValue() instanceof Object[]){
							values = (List<?>)Arrays.asList((Object[])e.getValue());
						}
						
						String value1 = DataUtils.convertToString(values.get(0));
						String value2 = DataUtils.convertToString(values.get(1));
						
						if(StringUtils.isNotBlank(value1) || StringUtils.isNotBlank(value2)){
							where += StringUtils.isNotBlank(e.getCondition()) ? e.getCondition() + " " : " " + criteria.getCondition() + " ";
							
							String s1 = "", s2 = "";
							if(SqlConstants.DataType.Date.equalsIgnoreCase(type)){
								if(StringUtils.isNotBlank(value1)){
									s1 = StringUtils.join("date_format(", columnName, ", '%Y%m%d') >= ?");
									params.add(formatDateTime(value1));
								}
								if(StringUtils.isNotBlank(value2)){
									s2 = StringUtils.join("date_format(", columnName, ", '%Y%m%d') <= ?");
									params.add(formatDateTime(value2));
								}
								String s = (StringUtils.isNotBlank(s1) && StringUtils.isNotBlank(s2)) ? StringUtils.join("(", s1 ," and ", s2, ")") : s1 + s2;
								where += s;
								
							}else{
								if(StringUtils.isNotBlank(value1)){
									s1 = StringUtils.join(columnName, " >= ?");
									params.add(value1);
								}
								if(StringUtils.isNotBlank(value2)){
									s2 = StringUtils.join(columnName, " <= ?");
									params.add(value2);
								}
								String s = (StringUtils.isNotBlank(s1) && StringUtils.isNotBlank(s2)) ? StringUtils.join("(", s1 ," and ", s2, ")") : s1 + s2;
								where += s;
//								println where
							}
						}
					}else if(SqlConstants.Operator.In.equalsIgnoreCase(e.getOperator())){
						List<?> values = (List<?>)e.getValue();
						
						List<String> holders = new LinkedList<>();
						for(Object it : values){
							if(StringUtils.isNotBlank(DataUtils.convertToString(it))){
								holders.add("?");
								params.add(it);
							}
						}
						if(CollectionUtils.isNotEmpty(holders)){
							where += StringUtils.isNotBlank(e.getCondition()) ? e.getCondition() + " " : " " + criteria.getCondition() + " ";
							where += StringUtils.join(columnName, " in (", StringUtils.join(holders, ","), ")");
						}
						
					}else{
						where += StringUtils.isNotBlank(e.getCondition()) ? e.getCondition() + " " : " " + criteria.getCondition() + " ";
						
						if(SqlConstants.Operator.Like.equalsIgnoreCase(e.getOperator())){
							where += StringUtils.join("lower(", columnName, ") like ?");
							params.add(StringUtils.join("%", value, "%").toLowerCase());
						}else{
							if(SqlConstants.DataType.Date.equals(type)){
								where += StringUtils.join("date_format(", columnName, " '%Y%m%d') ", e.getOperator(), " ? ");
								params.add(formatDateTime(value));
							}else{
								where += StringUtils.join(columnName, " ", e.getOperator(), " ? ");
								params.add(value);
							}
						}
					}

	//				println e
				}
			}
		}
		
		if(StringUtils.isNotBlank(where)){
			where = where.trim();
			int index = where.indexOf(" ");
			where = " where " + where.substring(index + 1, where.length());
		}
		
		wc.setWhere(where);
		wc.setParams(params.toArray());
		
		return wc;
	}
	
	public static String parseOrder(QueryCriteria criteria){
		String sql = "";
		
		if(CollectionUtils.isNotEmpty(criteria.getOrder())){
			sql += " order by ";
			
			List<String> items = new LinkedList<>();
			
			Iterator<OrderItem> itr = criteria.getOrder().iterator();
			while(itr.hasNext()){
				OrderItem it = itr.next();
				String columnName = getColumnName(criteria, it);
				
				items.add(StringUtils.join(columnName, " ", it.getSort()));
			}
			
			sql += StringUtils.join(items, ",");
		}
		
		return sql;
	}
	
	protected static String getColumnName(Criteria criteria, FieldItem item){
		String columnName = criteria.isRawName() ? item.getField() : DomainUtils.convertToDBName(item.getField(), criteria.isLowerCase());
		
		if(StringUtils.isNotBlank(item.getTableAlias())){
			columnName = StringUtils.join(item.getTableAlias(), ".", columnName);
		}
		
		return columnName;
	}
	
	protected static String formatDateTime(Object date){
		if(date instanceof String){
			String text = DataUtils.convertToString(date).replaceAll("\\s+", "");
			text = StringUtils.replace(text, "-", "");
			text = StringUtils.replace(text, ":", "");
			text = StringUtils.replace(text, "/", "");
			text = StringUtils.replace(text, "\\", "");
			text = StringUtils.replace(text, ",", "");
			return text;
		}else{
			return DataUtils.convertToString(date);
		}
	}

}
